﻿
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient

Namespace DB
    'マスタ編集
    Public Class MssqlUtilities
        Public Structure STC_MeisaiTable
            'SQL文
            Private m_sql As String
            '列を表示するかどうか示す
            Private m_visible As Boolean()
            '列のタイトル
            Private m_title As String()
            '列の幅
            Private m_width As Integer()

            Public Sub New(ByVal _sql As String, ByVal _visible As Boolean(), ByVal _title As String(), ByVal _width As Integer())
                Me.m_sql = _sql
                Me.m_visible = _visible
                Me.m_title = _title
                Me.m_width = _width
            End Sub

            Public Property Sql() As String
                Get
                    Return m_sql
                End Get
                Set(ByVal value As String)
                    m_sql = value
                End Set
            End Property

            '列を表示するかどうか示す
            Public Property Visible() As Boolean()
                Get
                    Return m_visible
                End Get
                Set(ByVal value As Boolean())
                    m_visible = value
                End Set
            End Property
            '列のタイトル
            Public Property Title() As String()
                Get
                    Return m_title
                End Get
                Set(ByVal value As String())
                    m_title = value
                End Set
            End Property
            '列の幅
            Public Property Width() As Integer()
                Get
                    Return m_width
                End Get
                Set(ByVal value As Integer())
                    m_width = value
                End Set
            End Property
        End Structure

        Public Structure STC_Item
            '列ID
            Private m_itemId As String
            '列名
            Private m_itemName As String
            '列のデータ型
            Private m_type As SqlDbType
            '列のサイズ
            Private m_size As Integer

            '値とのオペレータ
            Private m_operat As String
            'パラメーターとのオペレータ
            Private m_sqlKey As String

            'パラメーター内容
            Private m_paramValue As String

            Public Sub New(ByVal _itemId As String, ByVal _itemName As String, ByVal _type As SqlDbType, ByVal _size As Integer, ByVal _sqlKey As String, ByVal _operat As String, _
             ByVal _paramValue As String)
                Me.m_itemId = _itemId
                Me.m_itemName = _itemName
                Me.m_type = _type
                Me.m_size = _size
                Me.m_operat = _operat
                Me.m_sqlKey = _sqlKey
                Me.m_paramValue = _paramValue
            End Sub

            '列ID
            Public Property ItemId() As String
                Get
                    Return " " & m_itemId & Operat & "@" & m_itemId.ToUpper()
                End Get
                Set(ByVal value As String)
                    m_itemId = value
                End Set
            End Property

            '列名
            Public Property ItemName() As String
                Get
                    Return m_itemName
                End Get
                Set(ByVal value As String)
                    m_itemName = value
                End Set
            End Property
            '列のデータ型
            Public Property Type() As SqlDbType
                Get
                    Return m_type
                End Get
                Set(ByVal value As SqlDbType)
                    m_type = value
                End Set
            End Property
            '列のサイズ
            Public Property Size() As Integer
                Get
                    Return m_size
                End Get
                Set(ByVal value As Integer)
                    m_size = value
                End Set
            End Property

            '値とのオペレータ
            Public Property Operat() As String
                Get
                    Return " " & m_operat & " "
                End Get
                Set(ByVal value As String)
                    m_operat = value
                End Set
            End Property

            'パラメーターとのオペレータ
            Public Property SqlKey() As String
                Get
                    Return " " & m_sqlKey & " "
                End Get
                Set(ByVal value As String)
                    m_sqlKey = value
                End Set
            End Property

            'パラメーターID
            Public ReadOnly Property ParamId() As String
                Get
                    Return "@" & m_itemId.ToUpper()
                End Get
            End Property

            'パラメーター内容
            Public Property ParamValue() As String
                Get
                    Return m_paramValue
                End Get
                Set(ByVal value As String)
                    m_paramValue = value
                End Set
            End Property
        End Structure

        'SQL文
        Private Const SQL_LIKE_A As String = "'%'+####+'%'"
        'LIKE ALL
        Private Const SQL_LIKE_S As String = "'%'+####"
        'LIKE START
        Private Const SQL_LIKE_E As String = "####+'%'"
        'LIKE END

#Region "帳票印刷"


#End Region

#Region "変換データ入力、データ変換、帳票印刷"
        '変換データ入力-[業務種別選択]画面用
        Public ReadOnly GYOMU As New STC_MeisaiTable("SELECT * FROM GYOMU ORDER by 業務種別", Nothing, Nothing, Nothing)

        '帳票印刷-[ＪＯＢ一覧]画面用
        'データ変換-[ＪＯＢ一覧]画面用
        Public ReadOnly CUSTOMER As New STC_MeisaiTable("SELECT * FROM CUSTOMER", Nothing, Nothing, Nothing)

        '帳票印刷-[お客様情報再編集]画面用
        Public ReadOnly JOBID As New STC_MeisaiTable("SELECT * FROM JOBID", Nothing, Nothing, Nothing)

#End Region

#Region "マスタ編集"
        'マスタ編集-[マスタ選択]画面用
        Public ReadOnly MST As New STC_MeisaiTable("SELECT MSTID,MSTNAME FROM tblMaster", New Boolean(1) {False, True}, New String(1) {"No.", "マスタ名"}, New Integer(1) {50, 250})

        Public MSTWhere As STC_Item() = New STC_Item(1) {New STC_Item("MSTID", "No.", SqlDbType.NChar, 3, "WHERE", "=", _
         Nothing), New STC_Item("MSTNAME", "マスタ名", SqlDbType.NVarChar, 50, "AND", "=", _
         Nothing)}

#End Region



        Public Function GetParam(ByVal arrItem As STC_Item()) As SqlParameter()
            Dim cnt As Integer = arrItem.Length
            Dim param As SqlParameter() = New SqlParameter(cnt - 1) {}
            Dim clsSQL As New MssqlHelper()
            For i As Integer = 0 To cnt - 1
                param.SetValue(clsSQL.CreateInParam(arrItem(i).ParamId, arrItem(i).ParamValue, arrItem(i).Type, arrItem(i).Size), i)
            Next

            Return param
        End Function
    End Class
End Namespace